// DBManager.cpp: implementation of the DBManager class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "TinyPP.h"
#include "DBManager.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
const char* DBManager::DBFile="../DB/PP.db";
DBManager* DBManager::pInstance=NULL;

class AutoDBCloser
{
public:
	AutoDBCloser(){};
	~AutoDBCloser(){
		if(DBManager::pInstance){
			delete DBManager::pInstance;
			DBManager::pInstance=NULL;
		}
	}	
};

AutoDBCloser g_autoDbCloser;

DBManager* DBManager::Instance(){
		if (!pInstance) {
			pInstance = new DBManager;
			pInstance->ConnectDB();
		}
		return pInstance;
	};


DBManager::DBManager()
{
	m_db=NULL;
	InitializeCriticalSection(&m_cs);
}

DBManager::~DBManager()
{
	CloseDB();		
	DeleteCriticalSection(&m_cs);
}

bool DBManager::InsertAFeed(RSSFeed* pFeed)
{	
	AutoDBThreadProtector _thread_safe(&m_cs);

	sqlite3_bind_int(m_stmtInsertFeed,1,pFeed->m_nFeedGroupID);
	sqlite3_bind_text(m_stmtInsertFeed,2,pFeed->m_strChannelLink.c_str(),-1,SQLITE_STATIC);
	sqlite3_bind_text(m_stmtInsertFeed,3,pFeed->m_strChannelTitle.c_str(),-1,SQLITE_STATIC);
	sqlite3_step(m_stmtInsertFeed);
	int nRet = sqlite3_reset(m_stmtInsertFeed);
	if(nRet != SQLITE_OK){
		m_strDBErrMsg=sqlite3_errmsg(m_db);
//		MessageBox(NULL,m_strDBErrMsg.c_str(),"Feed insert error",NULL);
		return false;
	}else{
		SetFeedID(pFeed);
		return true;
	}
}

bool DBManager::ConnectDB()
{
	int rc = sqlite3_open(DBFile, &m_db);
	if( rc ){
		MessageBox(NULL,sqlite3_errmsg(m_db),"Can't open database",MB_OK);
		sqlite3_close(m_db);
		return false;
	}else{
		PrepareStmt();
		return true;
	}
}
void DBManager::CloseDB()
{
	if(m_db){
		 sqlite3_close(m_db);
	}
}

bool DBManager::PrepareStmt()
{
	AutoDBThreadProtector _thread_safe(&m_cs);

	const char* szTail=0;
	return sqlite3_prepare(m_db,
		"insert into feed (feed_group_id,url,name) values(?,?,?);",
		-1,
		&m_stmtInsertFeed,
		&szTail
		)==SQLITE_OK  && 
		sqlite3_prepare(m_db,
		"insert into feed_group (name,description,parent_group) values(?,?,?);",
		-1,
		&m_stmtInsertGroup,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"select feed_group_id from feed_group where name=?;",
		-1,
		&m_stmtSelectGroupID,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"select feed_id from feed where url=?;",
		-1,
		&m_stmtSelectFeedID,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"select feed_group_id, name, description, parent_group from feed_group where feed_group_id=?;",
		-1,
		&m_stmtSelectGroupFromID,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"select feed_group_id from feed_group where parent_group=?;",
		-1,
		&m_stmtSelectChildrenGroups,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"select feed_id from feed where feed_group_id=?;",
		-1,
		&m_stmtSelectChildrenFeeds,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"select feed_id, feed_group_id, url, name, description from feed where feed_id=?;",
		-1,
		&m_stmtSelectFeedFromID,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"delete from feed where feed_id=?;",
		-1,
		&m_stmtDeleteFeed,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"delete from feed_group where feed_group_id=?;",
		-1,
		&m_stmtDeleteGroup,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"insert into item(feed_id,url,title,description,retrieved) values(?,?,?,?,?);",
		-1,
		&m_stmtInsertItem,
		&szTail
		) == SQLITE_OK && 
		sqlite3_prepare(m_db,
		"select url,title,description,retrieved from item where feed_id=?;",
		-1,
		&m_stmtSelectItem,
		&szTail
		) == SQLITE_OK;
}


bool DBManager::InsertAGroup(FeedGroup* pGroup)
{		
	AutoDBThreadProtector _thread_safe(&m_cs);
	
	sqlite3_bind_text(m_stmtInsertGroup,1,pGroup->m_strGroupName.c_str(),-1,SQLITE_STATIC);
	sqlite3_bind_text(m_stmtInsertGroup,2,pGroup->m_strDescription.c_str(),-1,SQLITE_STATIC);
	sqlite3_bind_int(m_stmtInsertGroup,3,pGroup->m_nParentGroupID);
	sqlite3_step(m_stmtInsertGroup);   
	int nRet = sqlite3_reset(m_stmtInsertGroup);
	if(nRet != SQLITE_OK){
		sqlite3_reset(m_stmtInsertGroup);
		m_strDBErrMsg=sqlite3_errmsg(m_db);
		MessageBox(NULL,m_strDBErrMsg.c_str(),"Feed group insert error",NULL);
		return false;
	}else{
		SetGroupID(pGroup);
		return true;
	}
}

// set the group ID for pGroup
bool DBManager::SetGroupID(FeedGroup* pGroup)
{	
	AutoDBThreadProtector _thread_safe(&m_cs);

	sqlite3_bind_text(m_stmtSelectGroupID,1,pGroup->m_strGroupName.c_str(),-1,SQLITE_STATIC);
	int nRet = sqlite3_step(m_stmtSelectGroupID);
	
	if (nRet == SQLITE_ROW)	{ // there exists at least one column
		pGroup->m_nFeedGroupID = sqlite3_column_int(m_stmtSelectGroupID,0);
		sqlite3_reset(m_stmtSelectGroupID);
		return true;
	}else if (nRet == SQLITE_DONE){ // no result
		sqlite3_reset(m_stmtSelectGroupID);
		m_strDBErrMsg="no result returned from database";
		MessageBox(NULL,m_strDBErrMsg.c_str(),"Group select error",NULL);
		return false;	
	}else{		
		sqlite3_reset(m_stmtSelectGroupID);
		m_strDBErrMsg=sqlite3_errmsg(m_db);
		MessageBox(NULL,m_strDBErrMsg.c_str(),"Group select error",NULL);
		return false;
	}
}



// set the feed ID for pFeed
bool DBManager::SetFeedID(RSSFeed* pFeed)
{	
	AutoDBThreadProtector _thread_safe(&m_cs);

	sqlite3_bind_text(m_stmtSelectFeedID,1,pFeed->m_strChannelLink.c_str(),-1,SQLITE_STATIC);
	int nRet = sqlite3_step(m_stmtSelectFeedID);
	
	if (nRet == SQLITE_ROW)	{ // there exists at least one column
		pFeed->m_nFeedID= sqlite3_column_int(m_stmtSelectFeedID,0);
		sqlite3_reset(m_stmtSelectFeedID);
		return true;
	}else if (nRet == SQLITE_DONE){ // no result
		sqlite3_reset(m_stmtSelectFeedID);
		m_strDBErrMsg="no result returned from database";
		MessageBox(NULL,m_strDBErrMsg.c_str(),"Feed select error",NULL);
		return false;	
	}else{		
		sqlite3_reset(m_stmtSelectFeedID);
		m_strDBErrMsg=sqlite3_errmsg(m_db);
		MessageBox(NULL,m_strDBErrMsg.c_str(),"Feed select error",NULL);
		return false;
	}
}

// get feed id by url: -1 for false
int DBManager::GetFeedIDByUrl(const char* strURL)
{	
	AutoDBThreadProtector _thread_safe(&m_cs);

	int nID = -1;
	sqlite3_bind_text(m_stmtSelectFeedID,1,strURL,-1,SQLITE_STATIC);
	int nRet = sqlite3_step(m_stmtSelectFeedID);
	
	if (nRet == SQLITE_ROW)	{ // there exists at least one column
		nID = sqlite3_column_int(m_stmtSelectFeedID,0);
		sqlite3_reset(m_stmtSelectFeedID);
	}else if (nRet == SQLITE_DONE){ // no result
		sqlite3_reset(m_stmtSelectFeedID);
		m_strDBErrMsg="no result returned from database";
		MessageBox(NULL,m_strDBErrMsg.c_str(),"Feed select error",NULL);
	}else{		
		sqlite3_reset(m_stmtSelectFeedID);
		m_strDBErrMsg=sqlite3_errmsg(m_db);
		MessageBox(NULL,m_strDBErrMsg.c_str(),"Feed select error",NULL);
	}
	return nID;
}


FeedGroup* DBManager::CreateGroupFromID(int nID)
{
	AutoDBThreadProtector _thread_safe(&m_cs);

	sqlite3_bind_int(m_stmtSelectGroupFromID,1,nID);
	int nRet = sqlite3_step(m_stmtSelectGroupFromID);
	
	if (nRet == SQLITE_ROW)	{ // there exists at least one column
		int feed_group_id = nID;
		const char* name = (const char*)sqlite3_column_text(m_stmtSelectGroupFromID,1);
		const char* description = (const char*)sqlite3_column_text(m_stmtSelectGroupFromID,2); 
		int parent_group = sqlite3_column_int(m_stmtSelectGroupFromID,3);
		FeedGroup* pGroup = new FeedGroup(name,description,parent_group,feed_group_id);
		sqlite3_reset(m_stmtSelectGroupFromID);
		return pGroup;
	}else if (nRet == SQLITE_DONE){ // no result
		sqlite3_reset(m_stmtSelectGroupFromID);
		m_strDBErrMsg="no result returned from database";
		return NULL;
	}else{		
		sqlite3_reset(m_stmtSelectGroupFromID);
		m_strDBErrMsg=sqlite3_errmsg(m_db);
		return NULL;
	}
}


void DBManager::GetChildrenGroups(int nID, vector<int>& Children)
{
	AutoDBThreadProtector _thread_safe(&m_cs);

	Children.clear();
	sqlite3_bind_int(m_stmtSelectChildrenGroups,1,nID);
	int nRet = sqlite3_step(m_stmtSelectChildrenGroups);
	while(nRet == SQLITE_ROW){
		int thisid = sqlite3_column_int(m_stmtSelectChildrenGroups,0);
		Children.push_back(thisid);
		nRet = sqlite3_step(m_stmtSelectChildrenGroups);
	}
	
	if (nRet != SQLITE_DONE){ 
		m_strDBErrMsg=sqlite3_errmsg(m_db);	
	}
	sqlite3_reset(m_stmtSelectChildrenGroups);
}


RSSFeed* DBManager::CreateFeedFromID(int nID)
{
	AutoDBThreadProtector _thread_safe(&m_cs);

	sqlite3_bind_int(m_stmtSelectFeedFromID,1,nID);
	int nRet = sqlite3_step(m_stmtSelectFeedFromID);
	
	if (nRet == SQLITE_ROW)	{ // there exists at least one column
		int feed_id = nID;
		int feed_group_id = sqlite3_column_int(m_stmtSelectFeedFromID,1); 
		const char* url = (const char*)sqlite3_column_text(m_stmtSelectFeedFromID,2);
		const char* name = (const char*)sqlite3_column_text(m_stmtSelectFeedFromID,3);
		const char* description = (const char*)sqlite3_column_text(m_stmtSelectFeedFromID,4); 
		RSSFeed* pFeed= new RSSFeed(url, name, feed_group_id, description, feed_id);
		sqlite3_reset(m_stmtSelectFeedFromID);
		return pFeed;
	}else if (nRet == SQLITE_DONE){ // no result
		sqlite3_reset(m_stmtSelectFeedFromID);
		m_strDBErrMsg="no result returned from database";
		return NULL;
	}else{		
		sqlite3_reset(m_stmtSelectFeedFromID);
		m_strDBErrMsg=sqlite3_errmsg(m_db);
		return NULL;
	}
}

bool DBManager::GetFeedUrl(int nID,string &strUrl)
{
	AutoDBThreadProtector _thread_safe(&m_cs);

	sqlite3_bind_int(m_stmtSelectFeedFromID,1,nID);
	int nRet = sqlite3_step(m_stmtSelectFeedFromID);
	
	if (nRet == SQLITE_ROW)	{ // there exists at least one column
		const char* url = (const char*)sqlite3_column_text(m_stmtSelectFeedFromID,2);
		strUrl = url;
		sqlite3_reset(m_stmtSelectFeedFromID);
		return true;
	}else if (nRet == SQLITE_DONE){ // no result
		sqlite3_reset(m_stmtSelectFeedFromID);
		m_strDBErrMsg="no result returned from database";
		return false;
	}else{		
		sqlite3_reset(m_stmtSelectFeedFromID);
		m_strDBErrMsg=sqlite3_errmsg(m_db);
		return false;
	}
}


void DBManager::GetChildrenFeeds(int nID, vector<int>& Children)
{
	AutoDBThreadProtector _thread_safe(&m_cs);

	Children.clear();
	sqlite3_bind_int(m_stmtSelectChildrenFeeds,1,nID);
	int nRet = sqlite3_step(m_stmtSelectChildrenFeeds);
	while(nRet == SQLITE_ROW){
		int thisid = sqlite3_column_int(m_stmtSelectChildrenFeeds,0);
		Children.push_back(thisid);
		nRet = sqlite3_step(m_stmtSelectChildrenFeeds);
	}
	
	if (nRet != SQLITE_DONE){ 
		m_strDBErrMsg=sqlite3_errmsg(m_db);	
	}
	sqlite3_reset(m_stmtSelectChildrenFeeds);
}


bool DBManager::DeleteAFeed(RSSFeed* pFeed)
{
	return DeleteAFeed(pFeed->m_nFeedID);	
}


bool DBManager::DeleteAFeed(int nID)
{
	AutoDBThreadProtector _thread_safe(&m_cs);

	sqlite3_bind_int(m_stmtDeleteFeed,1,nID);
	int nRet = sqlite3_step(m_stmtDeleteFeed);
	
	if (nRet == SQLITE_DONE){ 
		sqlite3_reset(m_stmtDeleteFeed);
		return true;	
	}else{		
		sqlite3_reset(m_stmtDeleteFeed);
		m_strDBErrMsg=sqlite3_errmsg(m_db);
		MessageBox(NULL,m_strDBErrMsg.c_str(),"Feed select error",NULL);
		return false;
	}
}

bool DBManager::DeleteAGroup(FeedGroup* pGroup)
{
	return DeleteAGroup(pGroup->m_nFeedGroupID);
}

bool DBManager::DeleteAGroup(int nID)
{
	AutoDBThreadProtector _thread_safe(&m_cs);

	vector<int> Children;
	GetChildrenGroups(nID,Children);
	int i=0;
	for(i=0; i<Children.size();i++)
		DeleteAGroup(Children[i]);
	GetChildrenFeeds(nID,Children);	
	for(i=0; i<Children.size();i++)
		DeleteAFeed(Children[i]);

	sqlite3_bind_int(m_stmtDeleteGroup,1,nID);
	int nRet = sqlite3_step(m_stmtDeleteGroup);
	
	if (nRet == SQLITE_DONE){ 
		sqlite3_reset(m_stmtDeleteGroup);
		return true;	
	}else{		
		sqlite3_reset(m_stmtDeleteGroup);
		m_strDBErrMsg=sqlite3_errmsg(m_db);
		MessageBox(NULL,m_strDBErrMsg.c_str(),"Feed select error",NULL);
		return false;
	}
}

bool DBManager::InsetItems(int nID, vector<RSSItem>* pItems)
{
	AutoDBThreadProtector _thread_safe(&m_cs);

	CTime now = CTime::GetCurrentTime();
	CString strNow = now.Format("%Y-%m-%d %H:%M");

	for(vector<RSSItem>::iterator i=pItems->begin();i!=pItems->end();i++){	
		sqlite3_bind_int(m_stmtInsertItem,1,nID);
		sqlite3_bind_text(m_stmtInsertItem,2,i->link.c_str(),-1,SQLITE_STATIC);
		sqlite3_bind_text(m_stmtInsertItem,3,i->title.c_str(),-1,SQLITE_STATIC);
		sqlite3_bind_blob(m_stmtInsertItem,4,i->description.c_str(),i->description.size(),SQLITE_STATIC);
		sqlite3_bind_text(m_stmtInsertItem,5,strNow,-1,SQLITE_STATIC);
		int nRet = sqlite3_step(m_stmtInsertItem);
		if(nRet != SQLITE_DONE){
			sqlite3_reset(m_stmtInsertItem);
			m_strDBErrMsg=sqlite3_errmsg(m_db);
//			MessageBox(NULL,m_strDBErrMsg.c_str(),"Item insert error",NULL);
			return false;
		}
		nRet = sqlite3_reset(m_stmtInsertItem);
		if(nRet != SQLITE_OK){
			m_strDBErrMsg=sqlite3_errmsg(m_db);
//			MessageBox(NULL,m_strDBErrMsg.c_str(),"Item insert error",NULL);
			return false;
		}
	}
	return true;
}

bool DBManager::GetItems(int nID, vector<RSSItem>& Items)
{
	AutoDBThreadProtector _thread_safe(&m_cs);
	
	Items.clear();
	sqlite3_bind_int(m_stmtSelectItem,1,nID);
	int nRet = sqlite3_step(m_stmtSelectItem);
	while(nRet == SQLITE_ROW){
		const char* url = (const char*)sqlite3_column_text(m_stmtSelectItem,0);
		const char* title = (const char*)sqlite3_column_text(m_stmtSelectItem,1);
		const char* description = (const char*)sqlite3_column_blob(m_stmtSelectItem,2);
		
		Items.push_back(RSSItem(title,url,description));
		nRet = sqlite3_step(m_stmtSelectItem);
	}
	
	if (nRet != SQLITE_DONE){ 
		m_strDBErrMsg=sqlite3_errmsg(m_db);	
		sqlite3_reset(m_stmtSelectItem);
		return false;
	}
	sqlite3_reset(m_stmtSelectItem);
	return true;
}

bool DBManager::GetItems(RSSFeed* pFeed, vector<RSSItem>& Items)
{
	return GetItems(pFeed->m_nFeedID,Items);
}
